package com.example.graduationdesign.mapper;



import com.example.graduationdesign.pojo.dao.*;
import com.example.graduationdesign.pojo.dto.LineChartDto;
import com.example.graduationdesign.pojo.dto.ModifyEmailDto;
import com.example.graduationdesign.pojo.dto.RegisterDto;
import com.example.graduationdesign.pojo.vo.Jingdian;
import com.example.graduationdesign.pojo.vo.UserVo;
import com.example.graduationdesign.pojo.vo.Yuding;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * 公共接口的写作
 */
@Mapper
public interface CommonMapper {
    /**
     * 根据邮箱获取用户编码
     *
     * @param email
     * @return
     */
    @Select("select user_id from `user` where email = #{email} limit 1;")
    Long getUserIdByEmail(String email);

    /**
     * 根据用户编码修改用户密码
     *
     * @param password
     * @param userId
     * @return
     */
    @Update("update `user` set `password` = md5(#{password}) where user_id = #{userId};")
    Integer modifyUserPasswordByUserId(@Param("password") String password, @Param("userId") Long userId);

    /**
     * 根据角色编码查询角色信息
     *
     * @param roleId 角色编码
     * @return
     */
    @Select("select role_id, `name`, is_active from `role` where role_id = #{roleId};")
    RoleDao queryRoleByRoleId(@Param("roleId") Integer roleId);


    /**
     * 根据用户编码查询用户信息
     *
     * @param userId 用户编码
     * @return
     */
    @Select("select user_id, user_name, `password`,age,sex,phone,email, avatar, role_id, is_active from `user` where `user_id` = #{userId};")
    UserDao queryUserByUserId(@Param("userId") Long userId);



    /**
     * 通过用户的ID编号将用户所属的用户类型找出来即可
     * @param consumerId
     * @return
     */
    @Select("select consumer_variety,consumer_stage,consumer_score,current_level from consumer where consumer_id = #{consumerId};")
    ConsumerDao queryConsdumerVarietyByConsumerId(@Param("consumerId") Long consumerId);


    /**
     * 将注册的用户插入到数据库表user表中
     * @param registerDto
     * @return
     */
    @Insert("insert into user(user_name,password,age,sex,phone,email,avatar,role_id,city) values('',MD5(#{registerDto.password}),0,0,0,#{registerDto.email},'9b38b1d9-b3c3-499e-8601-f9d2b5e338f6.jpg',100,'重庆市');")
    Integer InsertUser(@Param("registerDto") RegisterDto registerDto);


    /**
     * 将用户信息插入到数据库中的consumer表中
     * @return
     */
    @Insert("insert into consumer(consumer_id,consumer_variety,consumer_score,consumer_stage,current_level) values(#{userId},0,0,1,0);")
    Integer InsertConsumer(@Param("userId") Long userId);


    /**
     * 用户获取用户的编码
     * @return
     */
    @Select("SELECT * FROM user\n" +
            "ORDER BY user_id DESC\n" +
            "LIMIT 1;")
    UserDao GetUserId();


    /**
     * 实现用户头像的更新
     * @param avatar
     * @param userId
     * @return
     */
    @Update("Update user set avatar = #{avatar} where user.user_id = #{userId};")
    Integer UpdateAvatarById(@Param("avatar") String avatar, @Param("userId") Long userId);


    /**
     * 用于跟新user表中的数据
     * @param userVo
     * @return
     */
    @Update("UPDATE user  set user_name = #{userVo.userName},age = #{userVo.age},sex = #{userVo.sex},phone = #{userVo.phone},email = #{userVo.email} where user_id = #{userVo.userId};")
    Integer UpdateUserInfomation(@Param("userVo") UserVo userVo);


    /**
     * 用于用户修改邮箱的操作
     * @param modifyEmailDto
     * @return
     */
    @Update("UPDATE user  set user_name = #{modifyEmailDto.userName},age = #{modifyEmailDto.age},sex = #{modifyEmailDto.sex},phone = #{modifyEmailDto.phone},email = #{modifyEmailDto.newEmail} where user_id = #{modifyEmailDto.userId};")
    Integer UpdateUserEmailInfomation(@Param("modifyEmailDto") ModifyEmailDto modifyEmailDto);

    /**
     * 更新用户的用户阶段
     * @param userVo
     * @return
     */
    @Update("UPDATE consumer  set consumer_stage = #{userVo.consumerStage} where consumer_id = #{userVo.userId};")
    Integer UpdateStage(@Param("userVo") UserVo userVo);


    /**
     * 更新管理员用户的信息
     * @param userVo
     * @return
     */
    @Update("UPDATE user  set user_name = #{userVo.userName},age = #{userVo.age},sex = #{userVo.sex},phone = #{userVo.phone},email = #{userVo.email} where user_id = #{userVo.userId};")
    Integer UpdateAdminInfomation(@Param("userVo") UserVo userVo);


    /**
     * 用于获取文章的内容
     * @return
     */
    @Select("select * from article")
    List<ArticleDao> GetArticle();

    /**
     * 查询邮箱是否存在
     * @param email
     * @return
     */
    @Select("SELECT * from user where email = #{email};")
    List<UserDao> GetIsExistEmail(@Param("email") String email);


    /**
     * 用于实现将用户的当前使用APP的IP地址写入数据库表中
     * @param userId
     * @param city
     * @return
     */
    @Update("UPDATE user set city = #{city} where user_id = #{userId}")
    Integer UpdateCity(@Param("userId") Long userId,@Param("city") String city);


    /**
     * 利用用户的邮箱获取用户的编码值
     * @param email
     * @return
     */
    @Select("select user_id from user where email = #{email};")
    Long GetAccount(@Param("email") String email);


    /**
     * 用于利用用户的邮箱获取用户编码
     * @param email
     * @return
     */
    @Select("select user_id from user where email = #{email};")
    Long GetConsumerId(@Param("email") String email);

    @Select("SELECT \n" +
            "    j.id AS jingdianId,\n" +
            "    j.jingdianmingcheng AS jingdianName,\n" +
            "    IFNULL(SUM(n.clicknum), 0) AS totalClicks,  -- 点击数\n" +
            "    IFNULL(COUNT(s.id), 0) AS totalStoreup,     -- 收藏数\n" +
            "    IFNULL(COUNT(d.id), 0) AS totalComments,    -- 评论数\n" +
            "    IFNULL(SUM(n.clicknum), 0) + IFNULL(COUNT(s.id), 0) + IFNULL(COUNT(d.id), 0) AS totalInteraction  -- 总互动数\n" +
            "FROM \n" +
            "    jingdianxinxi j\n" +
            "LEFT JOIN \n" +
            "    news n ON j.id = n.id  -- 假设新闻表中有景点的点击数\n" +
            "LEFT JOIN \n" +
            "    storeup s ON j.id = s.refid AND s.tablename = 'jingdianxinxi'  -- 景点收藏\n" +
            "LEFT JOIN \n" +
            "    discusslvyouchanpin d ON j.id = d.refid  -- 景点评论\n" +
            "GROUP BY \n" +
            "    j.id\n" +
            "ORDER BY \n" +
            "    totalInteraction DESC\n" +
            "LIMIT 10;")
    List<Jingdian> getJingdian();

    @Select("SELECT \n" +
            "    DATE_FORMAT(addtime, '%Y-%m-%d') AS day,\n" +
            "    COUNT(*) AS bookingCount\n" +
            "FROM \n" +
            "    lvyouyuding\n" +
            "WHERE \n" +
            "    addtime BETWEEN #{startTime} AND #{endTime}\n" +
            "GROUP BY \n" +
            "    DATE_FORMAT(addtime, '%Y-%m-%d')\n" +
            "ORDER BY \n" +
            "    day;")
    List<Yuding> getYuding(@Param("startTime") String startTime, @Param("endTime") String endTime);
}
